from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.mysql import DECIMAL
from hashlib import sha512
from functools import partial

engin = create_engine('mysql+pymysql://xieyuan:Magedu@2020@192.168.200.203/test')
Base = declarative_base()

__all__ = ['registor_user', 'user_login', 'input_achieve', 'read_achieve']

class User(Base):
    __tablename__ = 'user'
    
    id = Column(Integer, autoincrement=True, nullable=False, primary_key=True)
    name = Column(String, nullable=False)
    passwd = Column(String, nullable=False)
    role = Column(Integer, nullable=False)


class Achieve(Base):
    __tablename__ = 'achieve'

    id = Column(Integer, autoincrement=True, nullable=False, primary_key=True)
    name = Column(String, nullable=False)
    chinese = Column(DECIMAL(5, 2))
    math = Column(DECIMAL(5, 2))
    english = Column(DECIMAL(5, 2))

Session = sessionmaker(bind=engin)
session = Session()


def registor(session, table, name, passwd, role):
    passwd = sha512(passwd.encode()).hexdigest()
    if session.query(table).filter(table.name == name).first():
        raise ValueError('This user is already exist!')
    instant = User(name=name, passwd=passwd, role=role)
    session.add(instant)
    session.commit()
    return True

registor_user = partial(registor, session, User)
# registor_user(session, 'xieyuan3', '321321', 1)

def login(session, table, name, passwd):
    passwd = sha512(passwd.encode()).hexdigest()
    print(name, passwd)
    instants = session.query(table).filter(table.name == name).first()
    if not instants:
        return None

    if instants.passwd != passwd:
        return 0

    if instants.passwd == passwd:
        return instants.role

user_login = partial(login, session, User)

# print(user_login(session, User, 'xiaoming', '321321'))
# psession.query(User).all()

def input_achieve(session, name, chinese, math, english):
    instant = Achieve(name=name, chinese=chinese, math=math, english=english)
    session.add(instant)
    session.commit()

# input_achieve(session, Achieve, '老王', 60, 65, 70)
input_achieve = partial(input_achieve, session)

def read_achieve(session, table, name):
    instant = session.query(table).filter(table.name == name).first()
    if not instant:
        return None
    else:
        return {'chinese': int(instant.chinese), 'math': int(instant.math), 'english': int(instant.english)}


read_achieve = partial(read_achieve, session, Achieve)